﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using SqlCeManager.Tools;
using DevExpress.XtraGrid.Columns;
using System.Data.SqlServerCe;
using DevExpress.XtraGrid.Views.Grid;
using DevExpress.XtraEditors.Repository;

namespace SqlCeManager
{
    public partial class ImportDataForm : XtraForm
    {
        public ImportDataForm()
        {
            InitializeComponent();
        }
        GridColumn selCol = new GridColumn();
        ConnectionNode sourceNode = null;
        ConnectionNode destNode = null;
        DataSet sourceDataSet;
        private SqlCeDataAdapter sourceAdapter
        {
            get { return sourceNode.Adapter; }
        }
        private SqlCeDataAdapter destAdapter
        {
            get { return destNode.Adapter; }
        }
        private const string DestValColumn = "DestVal";
        private const string ZaznaczColumn = "Zaznacz";
        SqlCeCommandBuilder destBuilder;
        SqlCeCommand identityCmd;
        private Dictionary<string, SqlCeCommand> insertCommands = new Dictionary<string, SqlCeCommand>();
        private Dictionary<string, string> autoIncColumns = new Dictionary<string, string>();
        private Dictionary<string, List<string>> uniqueColumns = new Dictionary<string, List<string>>();
        private Dictionary<string, Dictionary<string, string[]>> fkeys = new Dictionary<string, Dictionary<string, string[]>>();

        private SqlCeCommand getInsertCommand(string table)
        {
            if (!insertCommands.ContainsKey(table))
            {
                destAdapter.SelectCommand.CommandText = "SELECT * FROM " + table;
                SqlCeCommandBuilder destBuilder = new SqlCeCommandBuilder(destAdapter);
                SqlCeCommand cmd = destBuilder.GetInsertCommand();
                insertCommands.Add(table, cmd);
                insertCommands[table].Transaction = transaction;
            }

            return insertCommands[table];
        }
        private string getAutoIncColumn(string table)
        {
            if (!autoIncColumns.ContainsKey(table))
            {
                string col = "";
                DataRow autoIncColRow = destNode.SchemaDataSet.COLUMNS.SelectFirstRow("AUTOINC_SEED IS NOT NULL AND TABLE_NAME = " + table.EscapeSql());
                if (autoIncColRow != null)
                {
                    col = autoIncColRow["COLUMN_NAME"].AsString();
                }
                autoIncColumns.Add(table, col);
            }
            return autoIncColumns[table];
        }

        public ImportDataForm(string sourceConnectionString, string destConnectionString)
        {
            InitializeComponent();
            selCol.Name = "selCol";
            selCol.Caption = "Zaznacz";
            selCol.UnboundType = DevExpress.Data.UnboundColumnType.Boolean;
            selCol.ColumnEdit = new DevExpress.XtraEditors.Repository.RepositoryItemCheckEdit();
            sourceNode = new ConnectionNode("", sourceConnectionString, null);
            destNode = new ConnectionNode("", destConnectionString, null);
            listTables.DataSource = sourceNode.SchemaDataSet.TABLES;
            listTables.ValueMember = "TABLE_NAME";
            listTables.DisplayMember = "TABLE_NAME";
            destBuilder = new SqlCeCommandBuilder(destAdapter);
            destAdapter.RowUpdated += new SqlCeRowUpdatedEventHandler(destAdapter_RowUpdated);
            identityCmd = new SqlCeCommand("SELECT @@IDENTITY", new SqlCeConnection(destConnectionString));
        }
        private void FillUnChangedRows(List<DataRow> list, DataRow r)
        {
            foreach (DataRelation rel in r.Table.ChildRelations)
            {
                DataRow[] rows = r.GetChildRows(rel);
                foreach (DataRow rr in rows)
                {
                    FillUnChangedRows(list, rr);
                    if (!list.Contains(rr) && rr.RowState != DataRowState.Added)
                        list.Add(rr);
                }
            }
        }
        void destAdapter_RowUpdated(object sender, SqlCeRowUpdatedEventArgs e)
        {
            if (e.Row.RowState == DataRowState.Added)
            {
                List<DataRow> list = new List<DataRow>();
                FillUnChangedRows(list, e.Row);

                string autoIncCol = getAutoIncColumn(e.Row.Table.TableName);

                if (autoIncCol.IsNotNull())
                {
                    e.Row[autoIncCol] = identityCmd.ExecuteScalar();
                }
                if (e.Row.Table.Columns.Contains(DestValColumn) && e.Row[DestValColumn].IsNull())
                {
                    if (autoIncCol.IsNotNull())
                    {
                        e.Row[DestValColumn] = e.Row[autoIncCol];
                    }
                    else
                    {
                        e.Row[DestValColumn] = e.Row[e.Row.Table.PrimaryKey[0]];
                    }
                }
                foreach (DataRow r in list)
                {
                    r.AcceptChanges();
                }
            }
            e.Row.AcceptChanges();
        }

        private void GetData()
        {
            fkeys.Clear();
            uniqueColumns.Clear();
            reps.Clear();
            levels.Clear();
            sourceDataSet = new DataSet();
            sourceDataSet.Tables.Add("TABLES").Columns.Add("Table");
            string table = "";
            foreach (DataRowView drv in listTables.CheckedItems)
            {
                table = drv.Row["TABLE_NAME"].AsString();
                if (!sourceDataSet.Tables["TABLES"].RowExists("Table = " + table.EscapeSql()))
                    sourceDataSet.Tables["TABLES"].Rows.Add(table);

                DataTable dt = new DataTable(table);
                dt.Columns.Add("Table").DefaultValue = table;
                dt.Columns.Add(ZaznaczColumn, typeof(bool)).DefaultValue = true;

                sourceAdapter.SelectCommand.CommandText = "SELECT * FROM " + table;
                sourceAdapter.Fill(dt);
                if (!sourceDataSet.Tables.Contains(table))
                    sourceDataSet.Tables.Add(dt);
                else
                    sourceDataSet.Tables[table].Merge(dt);

                sourceDataSet.Relations.Add("TABLES_" + table, sourceDataSet.Tables["TABLES"].Columns[0], sourceDataSet.Tables[table].Columns["Table"]);
            }
            List<string> tables = new List<string>();

            foreach (DataRowView drv in listTables.CheckedItems)
            {
                table = drv.Row["TABLE_NAME"].AsString();
                tables.Add(table);

                AddReferences(sourceDataSet.Tables[table]);
            }

            foreach (string s in tables)
            {
                if (sourceDataSet.Tables[s].ChildRelations != null &&
                    sourceDataSet.Tables[s].ChildRelations.Count > 0)
                {
                    foreach (DataRelation rel in sourceDataSet.Tables[s].ChildRelations)
                    {
                        if (rel.ChildTable.Columns.Contains("Table"))
                        {
                            string relName = "TABLES_" + rel.ChildTable.TableName;
                            if (sourceDataSet.Relations.Contains(relName))
                            {
                                sourceDataSet.Relations[relName].ChildKeyConstraint.DeleteRule = Rule.SetNull;

                                sourceDataSet.Relations.Remove(relName);
                            }

                            //rel.ChildTable.Columns.Remove("Table");
                            sourceDataSet.Tables["TABLES"].DeleteRows("Table = " + rel.ChildTable.TableName.EscapeSql());
                            sourceDataSet.Tables["TABLES"].AcceptChanges();

                        }
                        //if (rel.ChildTable.Columns.Contains(DestValColumn))
                        //{
                        //    rel.ChildTable.Columns.Remove(DestValColumn);
                        //}
                    }
                }
            }
            GetUniqueColumns();
            SetDestVal();
            gcMain.DataSource = sourceDataSet;
            gcMain.DataMember = "TABLES";
        }

        private void AddReferences(DataTable dt)
        {
            string table = dt.TableName;
            if (!fkeys.ContainsKey(table))
                fkeys.Add(table, new Dictionary<string, string[]>());

            DataRow[] refRows = sourceNode.SchemaDataSet.REFERENTIAL_CONSTRAINTS.Select("CONSTRAINT_TABLE_NAME = " + table.EscapeSql());
            foreach (DataRow r in refRows)
            {
                string uniqueTable = r["UNIQUE_CONSTRAINT_TABLE_NAME"].AsString();
                string constraintName = r["CONSTRAINT_NAME"].AsString();
                DataRow[] columns = sourceNode.SchemaDataSet.REFERENTIAL_CONSTRAINTS_COLUMNS.Select("CONSTRAINT_NAME = " + constraintName.EscapeSql());
                if (columns.Length == 1)
                {
                    string uniqueTableColumn = columns[0]["PRIMARY_COLUMN"].AsString();

                    string fkCol = columns[0]["FOREIGN_COLUMN"].AsString();
                    string inStr = "";
                    if (!fkeys[table].ContainsKey(fkCol))
                    {
                        fkeys[table].Add(fkCol, new string[] { uniqueTable, uniqueTableColumn });
                    }
                    List<object> list = new List<object>();
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr[fkCol].IsNotNull() && !list.Contains(dr[fkCol]))
                        {
                            list.Add(dr[fkCol]);
                            inStr = inStr.AppendString(",") + dr[fkCol].AsString().EscapeSql();
                        }
                    }
                    string select = "SELECT * FROM " + uniqueTable;
                    if (inStr.IsNotNull())
                    {
                        select += " WHERE " + uniqueTableColumn + " IN (" + inStr + ")";
                    }
                    else
                    {
                        select += " WHERE 1=0";
                    }
                    sourceAdapter.SelectCommand.CommandText = select;
                    DataTable refTable = new DataTable(uniqueTable);
                    //refTable.Columns.Add("Zaznacz", typeof(bool)).DefaultValue = true;
                    refTable.Columns.Add(DestValColumn);

                    sourceAdapter.Fill(refTable);

                    refTable.PrimaryKey = new DataColumn[] { refTable.Columns[uniqueTableColumn] };

                    if (!sourceDataSet.Tables.Contains(uniqueTable))
                    {
                        sourceDataSet.Tables.Add(refTable);
                    }
                    else
                    {
                        if (sourceDataSet.Tables[uniqueTable].PrimaryKey == null ||
                            sourceDataSet.Tables[uniqueTable].PrimaryKey.Length == 0)
                        {
                            sourceDataSet.Tables[uniqueTable].PrimaryKey = new DataColumn[] { sourceDataSet.Tables[uniqueTable].Columns[uniqueTableColumn] };
                        }

                        if (!sourceDataSet.Tables[uniqueTable].Columns.Contains(DestValColumn))
                        {
                            sourceDataSet.Tables[uniqueTable].Columns.Add(DestValColumn);
                        }
                        sourceDataSet.Tables[uniqueTable].Merge(refTable);
                    }
                    CreateRepository(uniqueTable, uniqueTableColumn);

                    string refName = "REF_" + dt.TableName + "_" + uniqueTable;
                    if (!sourceDataSet.Relations.Contains(refName))
                    {
                        DataRelation rel = sourceDataSet.Relations.Add(refName, sourceDataSet.Tables[uniqueTable].Columns[uniqueTableColumn], dt.Columns[fkCol]);
                        rel.ChildKeyConstraint.UpdateRule = Rule.Cascade;
                    }
                    if (!sourceDataSet.Tables["TABLES"].RowExists("Table = " + uniqueTable.EscapeSql()))
                    {
                        sourceDataSet.Tables["TABLES"].Rows.Add(uniqueTable);
                        if (!sourceDataSet.Tables[uniqueTable].Columns.Contains("Table"))
                        {
                            sourceDataSet.Tables[uniqueTable].Columns.Add("Table").DefaultValue = uniqueTable;
                            sourceDataSet.Relations.Add("TABLES_" + uniqueTable, sourceDataSet.Tables["TABLES"].Columns[0], sourceDataSet.Tables[uniqueTable].Columns["Table"]);

                            foreach (DataRow ur in sourceDataSet.Tables[uniqueTable].Rows)
                            {
                                if (ur["Table"].IsNull())
                                    ur["Table"] = uniqueTable;
                            }
                        }
                    }
                    AddReferences(sourceDataSet.Tables[uniqueTable]);
                }
            }
        }

        SqlCeTransaction transaction;

        private void UpdateTable(DataTable dt)
        {
            DataRow[] rows = null;
            if (dt.Columns.Contains(ZaznaczColumn))
                rows = dt.Select(ZaznaczColumn + " = 1");

            foreach (DataRow r in rows)
            {
                if (r.RowState != DataRowState.Added)
                {
                    r.AcceptChanges();
                    r.SetAdded();
                }
            }
            UpdateRows(rows);
            UpdateChildRows(rows);
        }

        private void UpdateChildRows(DataRow[] rows)
        {
            if (rows != null && rows.Length > 0)
            {

                foreach (DataRelation rel in rows[0].Table.ChildRelations)
                {
                    List<DataRow> list = new List<DataRow>();
                    foreach (DataRow r in rows)
                    {
                        list.AddRange(r.GetChildRows(rel));
                    }
                    if (list.Count > 0)
                    {
                        foreach (DataRow rr in list)
                            UpdateFk(rr, rows[0].Table.TableName);

                        destAdapter.InsertCommand = getInsertCommand(list[0].Table.TableName);
                        foreach (DataRow r in list.ToArray())
                        {
                            if (r.RowState == DataRowState.Modified)
                            {
                                r.AcceptChanges();
                            }
                        }
                        destAdapter.Update(list.ToArray());

                        UpdateChildRows(list.ToArray());
                    }
                }
            }
        }

        private void UpdateRows(params DataRow[] rows)
        {
            if (rows != null && rows.Length > 0)
            {
                if (rows[0].Table.TableName != "TABLES")
                {
                    foreach (DataRow r in rows)
                    {
                        UpdateFk(r, "");

                        if (r.RowState == DataRowState.Modified || r.RowState == DataRowState.Added)
                        {
                            if (r.Table.Columns.Contains(DestValColumn))
                            {
                                if (r[DestValColumn].IsNull())
                                {
                                    r.AcceptChanges();
                                    r.SetAdded();
                                }
                                else
                                {
                                    r.AcceptChanges();
                                }
                            }
                        }
                    }
                    destAdapter.InsertCommand = getInsertCommand(rows[0].Table.TableName);
                    destAdapter.Update(rows);
                }
            }
        }

        private void UpdateFk(DataRow r, string parenttable)
        {
            string table = r.Table.TableName;
            if (fkeys.ContainsKey(table))
            {
                Dictionary<string, string[]> fk = fkeys[table];
                foreach (KeyValuePair<string, string[]> kvp in fk)
                {
                    string col = kvp.Key;
                    string pkTable = kvp.Value[0];
                    string pkCol =  kvp.Value[1];
                    if (pkTable != parenttable)
                    {
                        try
                        {
                            if (r[col].IsNotNull())
                            {
                                DataRow dr = sourceDataSet.Tables[pkTable].SelectFirstRow(pkCol + " = " + r[col].AsString().EscapeSql());
                                if (dr != null)
                                {
                                    if (dr[DestValColumn].IsNull())
                                    {
                                        if (dr.RowState != DataRowState.Added)
                                        {
                                            dr.AcceptChanges();
                                            dr.SetAdded();
                                        }

                                        UpdateRows(dr);
                                    }
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                    }
                }
            }
        }
        private void SetChildAdded(DataRow r)
        {
            foreach (DataRelation rel in r.Table.ChildRelations)
            {
                DataRow[] rows =  r.GetChildRows(rel);
                foreach (DataRow rr in rows)
                {
                    if (rr[ZaznaczColumn].AsBool() && rr.RowState == DataRowState.Unchanged)
                    {
                        rr.SetAdded();
                        SetChildAdded(rr);
                    }
                }
            }
        }

        private void PrepareDataSet()
        {
            sourceDataSet.AcceptChanges();
            string table = "";

            foreach (DataRowView drv in listTables.CheckedItems)
            {
                table = drv.Row["TABLE_NAME"].AsString();
                if (sourceDataSet.Tables["TABLES"].RowExists("TABLE = " + table.EscapeSql()))
                {
                    DataRow[] rows = sourceDataSet.Tables[table].Select(ZaznaczColumn + " = 1");
                    foreach (DataRow r in rows)
                    {
                        if (r.RowState == DataRowState.Unchanged)
                            r.SetAdded();
                        SetChildAdded(r);
                    }
                }
            }

            foreach (DataTable dt in sourceDataSet.Tables)
            {
                if (dt.TableName != "TABLES")
                {
                    dt.AcceptChanges();
                    if (dt.Columns.Contains(DestValColumn))
                    {
                        DataRow[] rows = dt.Select(DestValColumn + " IS NULL");
                        //foreach (DataRow r in rows)
                        //    r.SetAdded();
                        rows = dt.Select(DestValColumn + " IS NOT NULL");
                        foreach (DataRow r in rows)
                        {
                            r[r.Table.PrimaryKey[0]] = r[DestValColumn];
                            r.AcceptChanges();
                        }
                    }
                    else if (dt.Columns.Contains(ZaznaczColumn))
                    {
                        DataRow[] rows = dt.Select(ZaznaczColumn + " = 1");
                        foreach (DataRow r in rows)
                            r.SetAdded();
                    }
                    DataRow autoIncColRow = sourceNode.SchemaDataSet.COLUMNS.SelectFirstRow("AUTOINC_SEED IS NOT NULL AND TABLE_NAME = " + dt.TableName.EscapeSql());
                    if (autoIncColRow != null)
                    {
                        int id = -1;
                        string col = autoIncColRow["COLUMN_NAME"].AsString();
                        foreach (DataRow r in dt.Rows)
                        {
                            if (r.RowState == DataRowState.Added)
                            {
                                r[col] = id--;
                            }
                        }
                    }
                    foreach (DataRow r in dt.Rows)
                    {
                        if (r.RowState == DataRowState.Modified)
                            r.AcceptChanges();
                    }
                }
            }
        }
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            bool res = false;
            try
            {
                sourceDataSet.EnforceConstraints = false;
                gcMain.DataSource = null;

                destAdapter.SelectCommand.Connection.Open();
                transaction = destAdapter.SelectCommand.Connection.BeginTransaction();
                identityCmd.Connection = destAdapter.SelectCommand.Connection;
                identityCmd.Transaction = transaction;

                PrepareDataSet();
                insertCommands.Clear();

                identityCmd.Transaction = transaction;

                string table = "";
                foreach (DataRowView drv in listTables.CheckedItems)
                {
                    table = drv.Row["TABLE_NAME"].AsString();
                    if (sourceDataSet.Tables["TABLES"].RowExists("Table = " + table.EscapeSql()))
                    {
                        UpdateTable(sourceDataSet.Tables[table]);
                    }
                }
                sourceDataSet = null;
                res = true;
            }
            catch (Exception ex)
            {
                res = false;
                ex.Log();
            }
            if (transaction != null)
            {
                if (res)
                {
                    transaction.Commit();
                    this.ShowDxInfoDialog("Importowanie powiodło się.");
                }
                else
                {
                    transaction.Rollback();
                    this.ShowDxErrorDialog("Błąd importowania.");
                }
                destAdapter.SelectCommand.Connection.Close();
            }
        }
        

        private void btnGetData_Click(object sender, EventArgs e)
        {
            GetData();
        }

        private void listTables_ItemChecking(object sender, DevExpress.XtraEditors.Controls.ItemCheckingEventArgs e)
        {
            gcMain.DataSource = null;
            sourceDataSet = null;
        }

        private List<string> levels = new List<string>();
        private Dictionary<string, RepositoryItemLookUpEdit> reps = new Dictionary<string, RepositoryItemLookUpEdit>();

        private void gcMain_ViewRegistered(object sender, DevExpress.XtraGrid.ViewOperationEventArgs e)
        {
            if (!levels.Contains(e.View.LevelName))
            {
                levels.Add(e.View.LevelName);
                //(e.View as GridView).CustomRowCellEdit += new CustomRowCellEditEventHandler(ImportDataForm_CustomRowCellEdit);
                (e.View as GridView).ShowingEditor += new CancelEventHandler(ImportDataForm_ShowingEditor);
            }
        }

        void ImportDataForm_ShowingEditor(object sender, CancelEventArgs e)
        {
            if ((sender as GridView).FocusedColumn.FieldName != ZaznaczColumn &&
                (sender as GridView).FocusedColumn.FieldName != DestValColumn)
            {
                e.Cancel = true;
            }
        }


        private void CreateRepository(string table, string column)
        {
            if (!reps.ContainsKey(table))
            {
                RepositoryItemLookUpEdit rep = new RepositoryItemLookUpEdit();
                rep.Buttons.Add(new DevExpress.XtraEditors.Controls.EditorButton(DevExpress.XtraEditors.Controls.ButtonPredefines.Ellipsis));
                rep.NullText = "";
                rep.NullValuePrompt = "";
                DataTable dt =new DataTable(table);
                destAdapter.SelectCommand.CommandText = "SELECT * FROM " + table;
                destAdapter.Fill(dt);
                rep.DataSource = dt;
                rep.PopulateColumns();
                rep.ValueMember = column;
                rep.DisplayMember = column;
                reps.Add(table, rep);
            }
        }

        private void GetUniqueColumns()
        {
            foreach (DataTable dt in sourceDataSet.Tables)
            {
                if (dt.TableName != "TABLES")
                    GetUniqueColumns(dt.TableName);
            }
        }
        private void GetUniqueColumns(string table)
        {
            if (!uniqueColumns.ContainsKey(table))
            {
                DataRow[] indexes = sourceNode.SchemaDataSet.INDEXES.Select("TABLE_NAME = " + table.EscapeSql());
                List<string> list = new List<string>();
                foreach (DataRow index in indexes)
                {
                    list.Clear();
                    DataRow[] columns =  sourceNode.SchemaDataSet.INDEXES_COLUMNS.Select("INDEX_NAME = " + index["INDEX_NAME"].AsString().EscapeSql());
                    foreach (DataRow col in columns)
                    {
                        list.Add(col["COLUMN_NAME"].AsString());
                        if (sourceNode.SchemaDataSet.COLUMNS.SelectFirstRow("TABLE_NAME = " + table.EscapeSql() + " AND COLUMN_NAME = " + col["COLUMN_NAME"].AsString().EscapeSql() +
                            " AND AUTOINC_SEED IS NOT NULL") != null)
                        {
                            list.Clear();
                            break;
                        }
                    }
                    if (list.Count > 0)
                    {
                        uniqueColumns.Add(table, list);
                        break;
                    }
                }
            }

        }

        private void SetDestVal(DataTable dt)
        {
            if (dt.Columns.Contains(DestValColumn) &&
                 dt.PrimaryKey != null &&
                  dt.PrimaryKey.Length == 1 &&
                  reps.ContainsKey(dt.TableName) && uniqueColumns.ContainsKey(dt.TableName))
            {
                DataTable destTable = reps[dt.TableName].DataSource as DataTable;
                List<string> uniqueCols = uniqueColumns[dt.TableName];
                string pkCol = dt.PrimaryKey[0].ColumnName;

                foreach (DataRow r in dt.Rows)
                {
                    if (r[DestValColumn].IsNull())
                    {
                        string select = "";
                        foreach (string col in uniqueCols)
                        {
                            select = select.AppendAnd();
                            select += col + " = " + r[col].AsString().EscapeSql();
                        }
                        DataRow dr = destTable.SelectFirstRow(select);
                        if (dr != null)
                            r[DestValColumn] = dr[pkCol];
                    }
                }
            }
        }
        private void SetDestVal()
        {
            foreach (DataTable dt in sourceDataSet.Tables)
            {
                if (dt.TableName != "TABLES")
                    SetDestVal(dt);
            }
        }
    }
}
